package ee.graphicscard.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import ee.graphicscard.model.db.ConnectionManager;
import ee.graphicscard.model.dto.GraphicsCard;

public class GraphicsCardDAOImpl implements GraphicsCardDAO
{
	private Logger logger = Logger.getLogger(GraphicsCardDAOImpl.class);
	ResultSet resultSetOfGraphicsCards;
	String sql;
	Statement statement;
	PreparedStatement preparedStatement;
	GraphicsCard[] arrayOfGraphicsCards;
	GraphicsCard currentGraphicsCard;
	

	public GraphicsCard[] findAll() 
	{
		ConnectionManager connectionManager = ConnectionManager.getInstance();
		Connection dbConnection = ConnectionManager.getInstance().getConnection();
		List<GraphicsCard> listOfGraphicsCards = new ArrayList<GraphicsCard>();
		sql = "SELECT graphicscard_id, product_name, cudacores, overview FROM graphicscard ORDER BY graphicscard_id";
		try 
		{
			//Because it is just statement used to read data (consists no placeholders), we do'nt need PrepareStatement object
			//Ordinary Statement object will handle the demands.
			statement = dbConnection.createStatement( 
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
			);
			resultSetOfGraphicsCards = statement.executeQuery(sql);
			//place the corsor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSetOfGraphicsCards.beforeFirst();
			
			int numberOfRows = 0;
			
			while (resultSetOfGraphicsCards.next())
			{
				currentGraphicsCard = new GraphicsCard();
				
				currentGraphicsCard.setGraphicscard_id(resultSetOfGraphicsCards.getInt("graphicscard_id"));
				currentGraphicsCard.setProduct_name(resultSetOfGraphicsCards.getString("product_name"));
				currentGraphicsCard.setCudacores(resultSetOfGraphicsCards.getInt("cudacores"));
				currentGraphicsCard.setOverview(resultSetOfGraphicsCards.getString("overview"));
				listOfGraphicsCards.add(currentGraphicsCard);
				numberOfRows++;
			}
			
			arrayOfGraphicsCards = new GraphicsCard[numberOfRows];
			for (int i=0; i<listOfGraphicsCards.size(); i++)
			{
				arrayOfGraphicsCards[i] = listOfGraphicsCards.get(i);
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			connectionManager.closeStatement(statement);
			connectionManager.closeResultSet(resultSetOfGraphicsCards);
			connectionManager.closeConnection();
		}
		
		logger.info("Executed statement: " + statement.toString());
		return arrayOfGraphicsCards;
	}

	
	public GraphicsCard findById(int graphicscard_id)
	{
		ConnectionManager connectionManager = ConnectionManager.getInstance();
		Connection dbConnection = ConnectionManager.getInstance().getConnection();
		
		sql = "SELECT graphicscard_id, product_name, cudacores, overview FROM graphicscard WHERE graphicscard_id = ?";
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			//filling the placeholders 
			preparedStatement.setInt(1, graphicscard_id);
			resultSetOfGraphicsCards = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read data)
			resultSetOfGraphicsCards.beforeFirst();
			
			if (resultSetOfGraphicsCards.next())
			{
				currentGraphicsCard = new GraphicsCard();
				currentGraphicsCard.setGraphicscard_id(resultSetOfGraphicsCards.getInt("graphicscard_id"));
				currentGraphicsCard.setProduct_name(resultSetOfGraphicsCards.getString("product_name"));
				currentGraphicsCard.setCudacores(resultSetOfGraphicsCards.getInt("cudacores"));
				currentGraphicsCard.setOverview(resultSetOfGraphicsCards.getString("overview"));
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			connectionManager.closeStatement(preparedStatement);
			connectionManager.closeResultSet(resultSetOfGraphicsCards);
			connectionManager.closeConnection();
		}
		
		logger.info("Executed statement: " + preparedStatement.toString());
		return currentGraphicsCard;
	}

	
	public boolean update(GraphicsCard updatedGraphicsCard)
	{
		ConnectionManager connectionManager = ConnectionManager.getInstance();
		Connection dbConnection = ConnectionManager.getInstance().getConnection();
		
		sql = "UPDATE graphicscard " +
				"SET product_name = ?, cudacores = ?, overview = ? " +
				"WHERE graphicscard_id = ?";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setString(1, updatedGraphicsCard.getProduct_name());
			preparedStatement.setInt(2, updatedGraphicsCard.getCudacores());
			preparedStatement.setString(3, updatedGraphicsCard.getOverview());
			preparedStatement.setInt(4, updatedGraphicsCard.getGraphicscard_id());
			
			//successful update means that the number of affected rows in a database must be exactly 1
			int affectedRows = preparedStatement.executeUpdate();
			preparedStatement.close();
			
			if (affectedRows == 1)
			{
				logger.warn("Edukas uuendamine lausega: " + preparedStatement.toString());
				return true;
			}
			else
			{
				logger.warn("Ebaedukas uuendamine lausega: " + preparedStatement.toString());
				return false;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return false;			
		}
		finally
		{
			connectionManager.closeStatement(preparedStatement);
			connectionManager.closeConnection();
		}
	}
}
